package com.jia.mapper;

import com.jia.entity.DepositUser;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jia.vo.admin.ComboVo;
import com.jia.vo.admin.DepositUserInfo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
* @author 贾天旺
* @description 针对表【t_deposit_user(退还押金记录)】的数据库操作Mapper
* @createDate 2024-04-26 18:59:46
* @Entity com.jia.entity.DepositUser
*/
public interface DepositUserMapper extends BaseMapper<DepositUser> {

    /**
     * 每日退还押金
     * @return
     */
    @Select("SELECT DATE(createtime) AS orderDate, COUNT(*) AS orderCount\n" +
            "FROM t_deposit_user\n" +
            "GROUP BY orderDate;")
    List<DepositUserInfo> depositUserInfo();

    /**
     * 每月
     * @return
     */

    @Select("\t\t\t\t\t\n" +
            "\t\tSELECT DATE_FORMAT(createtime,'%Y-%m') AS orderDate, COUNT(*) AS orderCount\n" +
            "            FROM t_deposit_user\n" +
            "            GROUP BY orderDate;\t")
    List<DepositUserInfo> DEPOSIT_USER_INFOSMonth();

    /**
     * 每年
     * @return
     */
    @Select("\t\tSELECT year(createtime) AS orderDate, COUNT(*) AS orderCount\n" +
            "            FROM t_deposit_user\n" +
            "            GROUP BY orderDate;")
    List<DepositUserInfo> DEPOSIT_USER_INFOSYear();

    /**
     * 美月套餐总额
     * @return
     */
    @Select("SELECT \n" +
            "    DATE_FORMAT(a.createtime, '%Y-%m') AS month,\n" +
            "    sum(a.money) AS amount\n" +
            "FROM \n" +
            "    t_combo_mid a\n" +
            "GROUP BY \n" +
            "    DATE_FORMAT(a.createtime, '%Y-%m')\n" +
            "ORDER BY \n" +
            "    month;")
    List<ComboVo> comboInfo();

    /**
     * 每日套餐信息
     * @return
     */
    @Select("SELECT \n" +
            "    DATE(a.createtime) AS month,\n" +
            "    sum(a.money) AS amount\n" +
            "FROM \n" +
            "    t_combo_mid a\n" +
            "GROUP BY \n" +
            "    DATE(a.createtime)\n" +
            "ORDER BY \n" +
            "    month;")
    List<ComboVo> comboInfoDay();


    /**
     * 每年套餐信息
     * @return
     */
    @Select("SELECT \n" +
            "    YEAR(a.createtime) AS month,\n" +
            "    sum(a.money) AS amount\n" +
            "FROM \n" +
            "    t_combo_mid a\n" +
            "GROUP BY \n" +
            "    YEAR(a.createtime)\n" +
            "ORDER BY \n" +
            "    month;")
    List<ComboVo> comboInfoYear();

    /**
     * 近七日
     * @return
     */
    @Select("SELECT \n" +
            "    DATE(a.createtime) AS month,\n" +
            "    SUM(a.money) AS amount\n" +
            "FROM \n" +
            "    t_combo_mid a\n" +
            "WHERE \n" +
            "    a.createtime > NOW() - INTERVAL 7 DAY\n" +
            "GROUP BY \n" +
            "    DATE(a.createtime)\n" +
            "ORDER BY \n" +
            "    month;\t")
    List<ComboVo> comboInfoDAY7();
}




